package io.renren.modules.app.utils;

import com.alibaba.fastjson.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Component;
import sun.misc.IOUtils;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @Description: TODO
 * @author: zlm
 * @date: 2020年08月25日 5:27 下午
 */
@Component
public class SqlUtils {
    @Value("classpath:static/json/test2.json")
    Resource addTaskJson;
    private final Logger logger = LoggerFactory.getLogger(this.getClass());

    public String  listToString(List list, String append) {
//        StringBuilder sb = new StringBuilder();
//        for (Object s: list){
//            sb.append(s).append(append);
//        }
//        return sb.toString();
        return String.join(append, list);//java8

    }
    public String  caseWhenToString(Map<String,String> valueMap, String keyName) {
        List caseWhen = new ArrayList();
        caseWhen.add("case");
        for (String key : valueMap.keySet()) {
            logger.debug(key+"   "+ valueMap.get(key));
            //WHEN A.paystatus = '1' THEN
            //		'无'
            caseWhen.add("when "+ keyName +" = '"+key+"' then '"+valueMap.get(key)+"' ");
        }
        caseWhen.add("END");
        return listToString(caseWhen," ");//java8

    }
    public String builder(String type) throws IOException{
        String jsonStr = new String(IOUtils.readFully(addTaskJson.getInputStream(), -1,true));
        JSONObject json = JSONObject.parseObject(jsonStr);
        JSONObject config = json.getJSONObject(type);
        int t = 1;
        String alien = "T";
        List fields = new ArrayList();
        List joinCondition = new ArrayList();
        List whereCondition = new ArrayList();
        List orderCondition = new ArrayList();
        Map tableMap= new HashMap<String,String>();
        String primalTableName = null;
        {
            JSONObject primalTable = config.getJSONObject("primalTable");
            List primalTableFieldKey = primalTable.getJSONArray("fieldKey");
            String alienT = alien+t;
            primalTableName =  primalTable.getString("tableName") + " "+ alienT;
            for (int i = 0; i < primalTableFieldKey.size(); i++) {
                logger.debug(i+"   "+ primalTableFieldKey.get(i));
                JSONObject primalTableField = (JSONObject)primalTableFieldKey.get(i);
                String keyName = primalTableField.getString("keyName");
                String as = primalTableField.getString("as");
                Map<String,String> valueMap = (Map<String,String>)primalTableField.get("valueMap");
                if(valueMap!=null){
                    fields.add( caseWhenToString(valueMap,alien+t+"."+keyName)+" as "+as);
                }else{
                    if(as!=null){
                        fields.add(alien+t+"."+keyName+" as "+as);
                    }else{
                        fields.add(alien+t+"."+keyName);
                    }
                }
                tableMap.put(primalTable.getString("tableName"),alienT);
            }
            t++;
        }
        {
            List joinTables = config.getJSONArray("joinTables");
            if(joinTables!=null){
                for (int j = 0; j < joinTables.size(); j++) {
                    JSONObject joinTable = (JSONObject)joinTables.get(j);
                    String tableName = joinTable.getString("tableName");
                    String tableKey = joinTable.getString("tableKey");
                    logger.debug(j + "   " + tableName);
                    List joinTableFieldKey = joinTable.getJSONArray("fieldKey");
                    String alienT = alien+t;
                    tableMap.put(tableName,alienT);
                    List<Object> join = new ArrayList();
                    join.add(joinTable.getString("joinType"));
                    join.add(tableName+" "+alienT);
                    join.add("on");
                    JSONObject primalTableKey = joinTable.getJSONObject("primalTableKey");
                    String joinTableName = primalTableKey.getString("tableName");
                    String joinTableKey = primalTableKey.getString("tableKey");
                    String joinAlienT = (String) tableMap.get(joinTableName);
                    join.add(alienT+"."+tableKey+" = "+joinAlienT+"."+joinTableKey);
                    String joinStr = listToString(join," ");
                    logger.debug(joinStr);
                    joinCondition.add(joinStr);
                    for (int i = 0; i < joinTableFieldKey.size(); i++) {
                        logger.debug(i+"   "+ joinTableFieldKey.get(i));
                        JSONObject joinTableField = (JSONObject)joinTableFieldKey.get(i);
                        String keyName = joinTableField.getString("keyName");
                        String as = joinTableField.getString("as");
                        Map<String,String> valueMap = (Map<String,String>)joinTableField.get("valueMap");
                        if(valueMap!=null){
                            fields.add( caseWhenToString(valueMap,alien+t+"."+keyName)+" as "+as);
                        }else{
                            if(as!=null){
                                fields.add(alien+t+"."+keyName+" as "+as);
                            }else{
                                fields.add(alien+t+"."+keyName);
                            }
                        }
                    }
                    t++;
                }
            }

        }
        {
            List wheres = config.getJSONArray("wheres");
            if(wheres!=null){
                for (int i = 0; i < wheres.size(); i++) {
                    JSONObject where = (JSONObject)wheres.get(i);

                    String tableName = where.getString("tableName");
                    logger.debug(i+"   "+ tableName);
                    whereCondition.add(tableMap.get(tableName)+"."+where.getString("key")+ " "+where.getString("condition")+" "+where.getString("value"));
                }
            }

        }
        {
            List orders = config.getJSONArray("orders");
            if(orders!=null){
                for (int i = 0; i < orders.size(); i++) {
                    JSONObject order = (JSONObject)orders.get(i);

                    String tableName = order.getString("tableName");
                    logger.debug(i+"   "+ tableName);
                    orderCondition.add(tableMap.get(tableName)+"."+order.getString("key")+ " "+order.getString("order"));
                }
            }


        }
        String fieldsStr = listToString(fields,",");
        String joinConditionStr = listToString(joinCondition," ");
        logger.info(fieldsStr);
        logger.info(primalTableName);
        logger.info(joinConditionStr);

        List sql = new ArrayList();
        sql.add("select");
        sql.add(fieldsStr);
        sql.add("from");
        sql.add(primalTableName);
        sql.add(joinConditionStr);

        if(whereCondition.size()>0){
            String whereConditionStr = listToString(whereCondition," and ");
            logger.info(whereConditionStr);
            sql.add("where ");
            sql.add(whereConditionStr);
        }
        if(orderCondition.size()>0){
            String orderConditionStr = listToString(orderCondition," , ");
            logger.info(orderConditionStr);
            sql.add("order by ");
            sql.add(orderConditionStr);
        }
        String sqlStr = listToString(sql," ");
        logger.info(sqlStr);
        return sqlStr;

    }
}
